package com.ruoyi.order.service.impl;

import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import com.github.pagehelper.PageHelper;
import com.google.common.collect.Lists;
import com.ruoyi.common.exception.BusinessException;
import com.ruoyi.common.utils.IdUtils;
import com.ruoyi.common.utils.bean.StreamUtils;
import com.ruoyi.order.enumration.MedicineUnitEnum;
import com.ruoyi.order.enumration.ProcessObjectUnitEnum;
import com.ruoyi.order.pojo.*;
import com.ruoyi.system.domain.SysDictData;
import com.ruoyi.system.pojo.HandleProjectCategory;
import com.ruoyi.system.service.ISysDictDataService;
import com.ruoyi.system.service.ISysUserService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.order.mapper.OrderMapper;
import com.ruoyi.order.domain.Order;
import com.ruoyi.order.service.IOrderService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

/**
 * 【请填写功能名称】Service业务层处理
 *
 * @author 1029432816@qq.com
 * @date 2020-02-25
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class OrderServiceImpl implements IOrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private ISysUserService iSysUserService;

    @Autowired
    private ISysDictDataService dictDataService;

    /**
     * 查询【请填写功能名称】
     *
     * @param id 【请填写功能名称】ID
     * @return 【请填写功能名称】
     */
    @Override
    public Order selectOrderById(Long id) {
        return orderMapper.selectOrderById(id);
    }

    /**
     * 查询【请填写功能名称】列表
     *
     * @param order 【请填写功能名称】
     * @return 【请填写功能名称】
     */
    @Override
    public List<Order> selectOrderList(Order order) {
        return orderMapper.selectOrderList(order);
    }

    @Override
    public synchronized int insertOrder(OrderDTO orderDTO) throws ParseException {
        Order order = new Order();
        BeanUtils.copyProperties(orderDTO, order);
        order.setCreateTime(new Date());
        order.setId(IdUtils.getId());
        order.setNumber(this.getNumber(orderDTO.getCompanyId()));
        return orderMapper.insertOrder(order);
    }

    @Override
    public int updateOrder(OrderDTO orderDTO) {
        Assert.notNull(orderDTO.getId(), "id不能为空");
        Order order = orderMapper.selectOrderById(orderDTO.getId());
        Assert.notNull(order, "订单记录不存在");
        BeanUtils.copyProperties(orderDTO, order);
        order.setUpdateTime(new Date());
        return orderMapper.updateOrder(order);
    }

    /**
     * 删除【请填写功能名称】对象
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    @Override
    public int deleteOrderByIds(List<Long> ids) {
        return orderMapper.deleteOrderByIds(ids);
    }

    /**
     * 删除【请填写功能名称】信息
     *
     * @param id 【请填写功能名称】ID
     * @return 结果
     */
    @Override
    public int deleteOrderById(Long id) {
        return orderMapper.deleteOrderById(id);
    }

    @Override
    public String getNumber(Long companyId) throws ParseException {
        SimpleDateFormat start = new SimpleDateFormat("yyyyMMdd 00:00:00");
        String startDateStr = start.format(new Date());
        Date startDate = start.parse(startDateStr);
        StringBuffer number = new StringBuffer();
        number.append("HY-");
        Assert.notNull(companyId, "公司id不能为空");
        SysDictData sysDictData = dictDataService.selectDictDataById(companyId);
        Assert.notNull(sysDictData, "公司所在地未配置");
        Assert.hasLength(sysDictData.getDictValue(), "公司所在地配置失效");

        Integer count = orderMapper.getCountByStartDate(startDate);
        count++;

        number.append(sysDictData.getDictValue())
                .append("-")
                .append(startDateStr, 2, 8)
                .append(StringUtils.leftPad(count.toString(), 3, "0"));

        return number.toString();
    }

    @Override
    public OrderVO getOrderVOById(Long id) {
        return orderMapper.getOrderVOById(id);
    }

    @Override
    public List<OrderVO> list(OrderListDTO orderListDTO) {
        if (StringUtils.isEmpty(orderListDTO.getOrderByField())) {
            orderListDTO.setOrderByField("o.create_time");
            orderListDTO.setAsc("desc");
        }
        PageHelper.startPage(orderListDTO.getCurrentPage(), orderListDTO.getPageSize(), orderListDTO.getOrderByField() + " " + orderListDTO.getAsc());
        return orderMapper.list(orderListDTO);
    }

    @Override
    public void getDisinfectionStatisticsReport(DisinfectionReportDTO disinfectionReportDTO, HttpServletResponse response) throws Exception {
        List<DisinfectionReportVO> disinfectionReportVOS = orderMapper.getDisinfectionStatisticsReport(disinfectionReportDTO);
        disinfectionReportVOS = disinfectionReportVOS.stream().filter(e -> !(e.getFirstUnit().equals("20`拼") || e.getSecondUnit().equals("40`拼"))).collect(Collectors.toList());

        if (CollectionUtils.isEmpty(disinfectionReportVOS)) {
            disinfectionReportVOS = new LinkedList<>();
//            response.setStatus(500);
//            throw new BusinessException("不存在可以导出的数据");
        }
        List<HandleProjectCategory> allProjectCategory = dictDataService.getAllProjectCategory("handle_project_categories");
        TreeMap<String, Map<String, Object>> disinfectionReportVOMap = new TreeMap<>();
        for (HandleProjectCategory projectCategory : allProjectCategory) {
            String dictLabel = projectCategory.getDictLabel();
            List<HandleProjectCategory.HandleProjectCategorySecond> categorySecondList = projectCategory.getCategorySecondList();
            if (CollectionUtils.isEmpty(categorySecondList)) {
                continue;
            }
            TreeMap<String, Object> secondMap = new TreeMap<>();
            for (HandleProjectCategory.HandleProjectCategorySecond projectCategorySecond : categorySecondList) {
                String secondDictLabel = projectCategorySecond.getDictLabel();
                List<HandleProjectCategory.HandleProjectCategorySecond.HandleProjectCategoryThird> categoryThirdList = projectCategorySecond.getCategoryThirdList();
                if (CollectionUtils.isEmpty(categoryThirdList)) {
                    List<DisinfectionReportVO> reportVOS = disinfectionReportVOS.stream().filter(e -> e.getStatisticsProcessObject().equals(secondDictLabel)).collect(Collectors.toList());
                    secondMap.put(secondDictLabel, reportVOS);
                    continue;
                }
                TreeMap<String, List<DisinfectionReportVO>> reportVOMap = new TreeMap<>();
                for (HandleProjectCategory.HandleProjectCategorySecond.HandleProjectCategoryThird categoryThird : categoryThirdList) {
                    String thirdDictLabel = categoryThird.getDictLabel();
                    List<DisinfectionReportVO> reportVOS = disinfectionReportVOS.stream().filter(e -> e.getStatisticsProcessObject().equals(thirdDictLabel)).collect(Collectors.toList());
                    reportVOMap.put(thirdDictLabel, reportVOS);
                }
                secondMap.put(secondDictLabel, reportVOMap);
            }
            disinfectionReportVOMap.put(dictLabel, secondMap);
        }

        DisinfectionReportExportVO disinfectionReportExportVO = new DisinfectionReportExportVO();
        SysDictData sysDictData = dictDataService.selectDictDataById(disinfectionReportDTO.getCompanyId());
        Assert.notNull(sysDictData, "公司配置不存在");
        disinfectionReportExportVO.setCompanyName(sysDictData.getDictLabel());
        List<DisinfectionReportExportVO.ProcessProjectFirst> firstList = new LinkedList<>();
        disinfectionReportExportVO.setProcessProjectFirstList(firstList);
        List<SysDictData> medicineList = dictDataService.selectDictDataByType("medicine");
        String medicineCategory = disinfectionReportDTO.getMedicineCategory();
        medicineList = medicineList.stream().filter(e -> medicineCategory.equals(e.getDictValue())).collect(Collectors.toList());
        Assert.notEmpty(medicineList, "请先配置药剂");
        for (Map.Entry<String, Map<String, Object>> processProjectFirstMap : disinfectionReportVOMap.entrySet()) {
            DisinfectionReportExportVO.ProcessProjectFirst first = new DisinfectionReportExportVO.ProcessProjectFirst();
            first.setName(processProjectFirstMap.getKey());
            List<DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond> secondList = new LinkedList<>();
            first.setProcessProjectSecondList(secondList);
            Map<String, Object> processProjectSecondMap = processProjectFirstMap.getValue();
            for (Map.Entry<String, Object> secondEntry : processProjectSecondMap.entrySet()) {
                DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond second = new DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond();
                second.setName(secondEntry.getKey());
                List<DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject> objectList = new LinkedList<>();
                second.setProcessObjectList(objectList);

                if (!(secondEntry.getValue() instanceof Map)) {
                    List<DisinfectionReportVO> reportVOList = (List<DisinfectionReportVO>) secondEntry.getValue();
                    second.setSubTotalOrderQuantity(reportVOList.size());
                    second.setSubTotalProcessQuantity(this.getProcessQuantity(first.getName(), second.getName(), reportVOList, disinfectionReportDTO.getMedicineCategory()));
                    double goodsQuantity = reportVOList.stream().mapToDouble(e -> "吨".equals(e.getThirdUnit()) ? e.getThirdQuantity() : 0).sum();
                    second.setSubTotalGoodsQuantity(goodsQuantity);

                    TreeMap<String, BigDecimal> medicineMap = new TreeMap<>();
                    for (SysDictData medicine : medicineList) {
                        double sum = reportVOList.stream().filter(e -> e.getUsedElixir().equals(medicine.getDictLabel())).mapToDouble(DisinfectionReportVO::getTotalMedication).sum();
                        medicineMap.put(medicine.getDictLabel(), this.getMedication(sum, medicine.getDictValue()));
                    }
                    second.setSubTotalMedicationMap(medicineMap);
                    secondList.add(second);
                    continue;
                }
                Map<String, List<DisinfectionReportVO>> processObjectMap = (Map<String, List<DisinfectionReportVO>>) secondEntry.getValue();
                for (Map.Entry<String, List<DisinfectionReportVO>> thirdEntry : processObjectMap.entrySet()) {
                    DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject object = new DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject();
                    object.setName(thirdEntry.getKey());
                    List<DisinfectionReportVO> reportVOList = thirdEntry.getValue();
                    object.setOrderQuantity(reportVOList.size());
                    object.setProcessQuantity(this.getProcessQuantity(first.getName(), second.getName(), reportVOList, disinfectionReportDTO.getMedicineCategory()));
                    double goodsQuantity = reportVOList.stream().mapToDouble(e -> "吨".equals(e.getThirdUnit()) ? e.getThirdQuantity() : 0).sum();
                    object.setGoodsQuantity(goodsQuantity);
                    TreeMap<String, BigDecimal> medicineMap = new TreeMap<>();
                    for (SysDictData medicine : medicineList) {
                        double sum = reportVOList.stream().filter(e -> e.getUsedElixir().equals(medicine.getDictLabel())).mapToDouble(DisinfectionReportVO::getTotalMedication).sum();
                        medicineMap.put(medicine.getDictLabel(), this.getMedication(sum, medicine.getDictValue()));
                    }
                    object.setMedicationMap(medicineMap);
                    objectList.add(object);
                }
                second.setSubTotalOrderQuantity(objectList.stream().mapToInt(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject::getOrderQuantity).sum());
                second.setSubTotalProcessQuantity(objectList.stream().mapToDouble(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject::getProcessQuantity).sum());
                second.setSubTotalGoodsQuantity(objectList.stream().mapToDouble(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject::getGoodsQuantity).sum());
                Map<String, BigDecimal> subTotalMedicationMap = this.mapListToMap(objectList.stream().map(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject::getMedicationMap).collect(Collectors.toList()));
                second.setSubTotalMedicationMap(new TreeMap<>(subTotalMedicationMap));
                secondList.add(second);
            }
            first.setTotalOrderQuantity(secondList.stream().mapToInt(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond::getSubTotalOrderQuantity).sum());
            first.setTotalProcessQuantity(secondList.stream().mapToDouble(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond::getSubTotalProcessQuantity).sum());
            first.setTotalGoodsQuantity(secondList.stream().mapToDouble(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond::getSubTotalGoodsQuantity).sum());
            Map<String, BigDecimal> totalMedicationMap = this.mapListToMap(secondList.stream().map(DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond::getSubTotalMedicationMap).collect(Collectors.toList()));
            first.setTotalMedicationMap(new TreeMap<>(totalMedicationMap));
            firstList.add(first);
        }
        String fileName = disinfectionReportDTO.getMedicineCategory() + "处理业务数据统计表";
        HSSFWorkbook sheets = this.exportExcel(disinfectionReportExportVO, disinfectionReportDTO, fileName);
        this.writeResponse(sheets, response, fileName);
    }

    private BigDecimal getMedication(double sum, String businessName) {
        if (businessName.equals("熏蒸")) {
            return BigDecimal.valueOf(sum).setScale(3);
        } else {
            return BigDecimal.valueOf(sum).divide(new BigDecimal(1000)).setScale(3);
        }
    }

    private double getProcessQuantity(String firstName, String secondName, List list, String businessName) {
        List<BusinessBaseReportVO> reportVOList = list;
        double processQuantity = 0;
        switch (firstName) {
            case "集装箱及装载货物":
                processQuantity = reportVOList.stream().mapToDouble(e -> e.getFirstQuantity() + e.getSecondQuantity() * 2).sum();

                break;
            case "散装货物":
                processQuantity = reportVOList.stream().filter(e -> "m3".equals(e.getThirdUnit())).mapToDouble(e -> e.getThirdQuantity()).sum();

                break;
            case "其他":
                if ("压舱水".equals(secondName)) {
                    processQuantity = reportVOList.stream().filter(e -> "吨".equals(e.getThirdUnit())).mapToDouble(e -> e.getThirdQuantity()).sum();
                } else {
                    processQuantity = reportVOList.stream().filter(e -> "m2".equals(e.getThirdUnit())).mapToDouble(e -> e.getThirdQuantity()).sum();
                }
                break;
            case "交通工具":
                if ("熏蒸".equals(businessName)) {
                    processQuantity = reportVOList.stream().filter(e -> "m3".equals(e.getThirdUnit())).mapToDouble(e -> e.getThirdQuantity()).sum();
                } else {
                    processQuantity = reportVOList.stream().filter(e -> "m2".equals(e.getThirdUnit())).mapToDouble(e -> e.getThirdQuantity()).sum();
                }
                break;
        }
        return processQuantity;
    }

    @Override
    public OrderVO getLatestOrder() {
        return orderMapper.getLatestOrder();
    }

    @Override
    public void getBusinessVolumeReport(BusinessVolumeReportDTO businessVolumeReportDTO, HttpServletResponse response) throws IOException {
        List<BusinessVolumeReportVO> businessVolumeReportList = orderMapper.getBusinessVolumeReport(businessVolumeReportDTO);
        businessVolumeReportList = businessVolumeReportList.stream().filter(e -> !(e.getFirstUnit().equals("20`拼") || e.getSecondUnit().equals("40`拼"))).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(businessVolumeReportList)) {
            businessVolumeReportList = new LinkedList<>();
        }
        List<SysDictData> medicineList = dictDataService.selectDictDataByType("business_type");
        Assert.notEmpty(medicineList, "业务种类不存在, 请先配置业务种类");
        List<HandleProjectCategory> allProjectCategory = dictDataService.getAllProjectCategory("handle_project_categories");

        Map<String, Map<String, List<BusinessVolumeReportVO>>> businessVolumeReportMap = new HashMap<>();
        for (HandleProjectCategory projectCategory : allProjectCategory) {
            String dictLabel = projectCategory.getDictLabel();
            List<HandleProjectCategory.HandleProjectCategorySecond> categorySecondList = projectCategory.getCategorySecondList();
            if (CollectionUtils.isEmpty(categorySecondList)) {
                continue;
            }
            Map<String, List<BusinessVolumeReportVO>> secondMap = new HashMap<>();
            for (HandleProjectCategory.HandleProjectCategorySecond projectCategorySecond : categorySecondList) {
                String secondDictLabel = projectCategorySecond.getDictLabel();
                List<BusinessVolumeReportVO> reportVOList = businessVolumeReportList.stream().filter(e -> e.getProcessProjectSecond().equals(secondDictLabel)).collect(Collectors.toList());
                secondMap.put(secondDictLabel, reportVOList);
            }
            businessVolumeReportMap.put(dictLabel, secondMap);
        }
        BusinessVolumeExportVO businessVolumeExportVO = new BusinessVolumeExportVO();
        SysDictData sysDictData = dictDataService.selectDictDataById(businessVolumeReportDTO.getCompanyId());
        Assert.notNull(sysDictData, "公司配置不存在");
        businessVolumeExportVO.setCompanyName(sysDictData.getDictLabel())
                .setCreateTimeStart(businessVolumeReportDTO.getCreateTimeStart())
                .setCreateTimeEnd(businessVolumeReportDTO.getCreateTimeEnd())
                .setInformant(businessVolumeReportDTO.getCreateBy());
        List<BusinessVolumeExportVO.ProjectFirst> projectFirstList = new LinkedList<>();
        for (Map.Entry<String, Map<String, List<BusinessVolumeReportVO>>> firstEntry : businessVolumeReportMap.entrySet()) {
            BusinessVolumeExportVO.ProjectFirst projectFirst = new BusinessVolumeExportVO.ProjectFirst();
            projectFirst.setName(firstEntry.getKey());
            List<BusinessVolumeExportVO.ProjectFirst.ProjectSecond> projectSecondList = new LinkedList<>();
            Map<String, List<BusinessVolumeReportVO>> secondMap = firstEntry.getValue();

            for (Map.Entry<String, List<BusinessVolumeReportVO>> secondEntry : secondMap.entrySet()) {
                BusinessVolumeExportVO.ProjectFirst.ProjectSecond projectSecond = new BusinessVolumeExportVO.ProjectFirst.ProjectSecond();
                projectSecond.setName(secondEntry.getKey());
                List<BusinessVolumeReportVO> volumeReportVOS = secondEntry.getValue();
                Map<String, BusinessVolumeExportVO.BusinessData> businessDataMap = new LinkedHashMap<>();
                for (SysDictData medicine : medicineList) {
                    List<BusinessVolumeReportVO> volumeReportVOList = volumeReportVOS.stream().filter(e -> e.getBusinessName().equals(medicine.getDictLabel())).collect(Collectors.toList());
                    BusinessVolumeExportVO.BusinessData businessData = new BusinessVolumeExportVO.BusinessData();
                    businessData.setTotalOrderQuantity(volumeReportVOList.size());
                    Double processQuantity = this.getProcessQuantity(projectFirst.getName(), projectSecond.getName(), volumeReportVOList, medicine.getDictLabel());
                    Double goodsQuantity = volumeReportVOList.stream().mapToDouble(e -> "吨".equals(e.getThirdUnit()) ? e.getThirdQuantity() : 0).sum();
                    businessData.setTotalProcessQuantity(processQuantity);
                    businessData.setTotalGoodsQuantity(goodsQuantity);

                    businessDataMap.put(medicine.getDictLabel(), businessData);
                }
                projectSecond.setBusinessDataMap(businessDataMap);

                projectSecondList.add(projectSecond);
            }
            projectFirst.setProjectSecondList(projectSecondList);

            Map<String, BusinessVolumeExportVO.BusinessData> businessDataMap = new LinkedHashMap<>();
            for (SysDictData medicine : medicineList) {
                BusinessVolumeExportVO.BusinessData businessData = new BusinessVolumeExportVO.BusinessData();
                Integer totalOrderQuantity = projectSecondList.stream().mapToInt(e -> e.getBusinessDataMap().get(medicine.getDictLabel()).getTotalOrderQuantity()).sum();
                businessData.setTotalOrderQuantity(totalOrderQuantity);
                Double totalProcessQuantity = projectSecondList.stream().mapToDouble(e -> e.getBusinessDataMap().get(medicine.getDictLabel()).getTotalProcessQuantity()).sum();
                businessData.setTotalProcessQuantity(totalProcessQuantity);
                Double totalGoodsQuantity = projectSecondList.stream().mapToDouble(e -> e.getBusinessDataMap().get(medicine.getDictLabel()).getTotalGoodsQuantity()).sum();
                businessData.setTotalGoodsQuantity(totalGoodsQuantity);

                businessDataMap.put(medicine.getDictLabel(), businessData);
            }
            projectFirst.setBusinessDataMap(businessDataMap);

            projectFirstList.add(projectFirst);
        }
        businessVolumeExportVO.setProjectFirstList(projectFirstList);

        String fileName = "检疫处理业务量统计上报表";
        HSSFWorkbook sheets = this.exportBusinessExcel(businessVolumeExportVO, medicineList, fileName);
        this.writeResponse(sheets, response, fileName);
    }

    @Override
    public void getDoseReport(BusinessVolumeReportDTO businessVolumeReportDTO, HttpServletResponse response) throws IOException {
        List<DoseReportVO> reportVOList = orderMapper.getDoseReport(businessVolumeReportDTO);
        reportVOList = reportVOList.stream().filter(e -> !(e.getFirstUnit().equals("20`拼") || e.getSecondUnit().equals("40`拼"))).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(reportVOList)) {
            reportVOList = new LinkedList<>();
        }
        List<SysDictData> medicineList = dictDataService.selectDictDataByType("medicine");
        Assert.notEmpty(medicineList, "业务种类不存在, 请先配置药剂");
        Map<String, List<SysDictData>> medicineMap = medicineList.stream().collect(Collectors.groupingBy(SysDictData::getDictValue));
        DoseExportVO doseExportVO = new DoseExportVO();
        List<SysDictData> projectCategories = dictDataService.selectDictDataByType("handle_project_categories");
        projectCategories = projectCategories.stream().filter(e -> e.getDictValue().equals("无")).collect(Collectors.toList());
        Assert.notEmpty(projectCategories, "处理项目一级分类不存在");
        List<DoseExportVO.BusinessVO> businessVOList = new LinkedList<>();
        for (Map.Entry<String, List<SysDictData>> entry : medicineMap.entrySet()) {
            DoseExportVO.BusinessVO businessVO = new DoseExportVO.BusinessVO();
            List<SysDictData> medicines = entry.getValue();
            String businessName = entry.getKey();
            List<DoseExportVO.BusinessVO.MedicineVO> medicineVOList = new LinkedList<>();
            for (SysDictData medicine : medicines) {
                DoseExportVO.BusinessVO.MedicineVO medicineVO = new DoseExportVO.BusinessVO.MedicineVO();
                medicineVO.setUnit(MedicineUnitEnum.getUnit(medicine.getDictLabel()));
                medicineVO.setUsedElixir(medicine.getDictLabel());
                List<DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO> projectFirstVOList = new LinkedList<>();
                for (SysDictData projectCategory : projectCategories) {
                    List<DoseReportVO> reportVOS = reportVOList.stream().filter(e -> e.getBusinessName().equals(businessName) &&
                            e.getUsedElixir().equals(medicine.getDictLabel()) &&
                            e.getBusinessName().equals(projectCategory.getDictLabel())).collect(Collectors.toList());
                    double totalMedication = reportVOS.stream().mapToDouble(DoseReportVO::getTotalMedication).sum();
                    DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO projectFirstVO = new DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO();
                    projectFirstVO.setTotalMedication(this.getMedication(totalMedication, businessName).doubleValue());
                    Set<String> unitSet = ProcessObjectUnitEnum.getUnitSet(projectCategory.getDictLabel());
                    double totalProcessQuantity = reportVOS.stream().mapToDouble(e -> {
                        if (unitSet.contains(e.getThirdUnit())) {
                            return e.getThirdQuantity();
                        }
                        if (unitSet.contains(e.getSecondUnit()) || unitSet.contains(e.getFirstUnit())) {
                            return e.getFirstQuantity() + e.getSecondQuantity() * 2;
                        }
                        return 0;
                    }).sum();
                    projectFirstVO.setTotalProcessQuantity(totalProcessQuantity);
                    if ("压舱水".equals(projectCategory.getDictLabel())) {
                        double ballastWaterWeight = reportVOS.stream().filter(e -> "吨".equals(e.getThirdUnit())).mapToDouble(DoseReportVO::getThirdQuantity).sum();
                        projectFirstVO.setBallastWaterWeight(ballastWaterWeight);
                    }
                    projectFirstVOList.add(projectFirstVO);
                }
                medicineVO.setProjectFirstVOList(projectFirstVOList);
                medicineVO.setTotalMedication(projectFirstVOList.stream().mapToDouble(DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO::getTotalMedication).sum());
                medicineVOList.add(medicineVO);
            }
            businessVO.setMedicineVOList(medicineVOList);
            businessVO.setBusinessName(businessName);
            businessVOList.add(businessVO);
        }
        doseExportVO.setBusinessVOList(businessVOList);
        doseExportVO.setInformant(businessVolumeReportDTO.getCreateBy());
        doseExportVO.setCreateTimeStart(businessVolumeReportDTO.getCreateTimeStart());
        doseExportVO.setCreateTimeEnd(businessVolumeReportDTO.getCreateTimeEnd());
        SysDictData sysDictData = dictDataService.selectDictDataById(businessVolumeReportDTO.getCompanyId());
        Assert.notNull(sysDictData, "公司配置不存在");
        doseExportVO.setCompanyName(sysDictData.getDictLabel());

        String fileName = "检疫处理业务用药量统计上报表";
        HSSFWorkbook sheets = exportDoseReport(doseExportVO, projectCategories, fileName);
        this.writeResponse(sheets, response, fileName);
    }

    @Override
    public void getTrafficChargeReport(BusinessVolumeReportDTO businessVolumeReportDTO, HttpServletResponse response) throws IOException {
        List<TrafficChargeReportVO> reportVOS = orderMapper.getTrafficChargeReport(businessVolumeReportDTO);
        reportVOS = reportVOS.stream().filter(e -> !(e.getFirstUnit().equals("20`拼") || e.getSecondUnit().equals("40`拼"))).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(reportVOS)) {
            reportVOS = new LinkedList<>();
        }
        TrafficChargeExportVO trafficChargeExportVO = new TrafficChargeExportVO();
        SysDictData sysDictData = dictDataService.selectDictDataById(businessVolumeReportDTO.getCompanyId());
        Assert.notNull(sysDictData, "公司配置不存在");
        trafficChargeExportVO.setCompanyName(sysDictData.getDictLabel());
        trafficChargeExportVO.setCreateTimeStart(businessVolumeReportDTO.getCreateTimeStart());
        trafficChargeExportVO.setCreateTimeEnd(businessVolumeReportDTO.getCreateTimeEnd());
        trafficChargeExportVO.setInformant(businessVolumeReportDTO.getCreateBy());
        List<TrafficChargeExportVO.Second> secondList = new LinkedList<>();

        List<TrafficChargeReportVO> otherProducts = reportVOS.stream().filter(e -> !("植物产品".equals(e.getProcessProjectSecond()) ||
                "动物产品".equals(e.getProcessProjectSecond()) ||
                "媒介防控".equals(e.getProcessProjectSecond()))).collect(Collectors.toList());

        List<String> firstCategories = Lists.newArrayList("交通工具", "集装箱及装载货物", "散装货物", "其它");
        List<SysDictData> medicineList = dictDataService.selectDictDataByType("medicine");
        Assert.notEmpty(medicineList, "业务种类不存在, 请先配置药剂");
        medicineList = medicineList.stream().filter(StreamUtils.distinctByKey(e -> e.getDictValue())).collect(Collectors.toList());
        secondList.add(this.getSecond("卫生检疫处理", otherProducts, firstCategories, medicineList));

        List<String> plantFirstCategories = Lists.newArrayList("集装箱及装载货物", "散装货物");
        List<TrafficChargeReportVO> plantProducts = reportVOS.stream().filter(e -> "植物产品".equals(e.getProcessProjectSecond())).collect(Collectors.toList());
        secondList.add(this.getSecond("植物产品检疫处理", plantProducts, plantFirstCategories, medicineList));

        List<TrafficChargeReportVO> animalProducts = reportVOS.stream().filter(e -> "动物产品".equals(e.getProcessProjectSecond())).collect(Collectors.toList());
        secondList.add(this.getSecond("动物产品检疫处理", animalProducts, plantFirstCategories, medicineList));

        List<TrafficChargeReportVO> mediaPrevention = reportVOS.stream().filter(e -> "媒介防控".equals(e.getProcessProjectSecond())).collect(Collectors.toList());
        secondList.add(this.getSecond("媒介防控处理", mediaPrevention, Lists.newArrayList("其他"), medicineList));

        trafficChargeExportVO.setSecondList(secondList);

        trafficChargeExportVO.setBusinessDataList(this.getTotalBusinessList(secondList.stream().map(TrafficChargeExportVO.Second::getBusinessList).collect(Collectors.toList())));

        String fileName = "检疫处理业务量收费统计上报表";
        HSSFWorkbook hssfWorkbook = this.getTrafficChargeHSSFWorkbook(trafficChargeExportVO, fileName, medicineList);

        this.writeResponse(hssfWorkbook, response, fileName);
    }

    private List<TrafficChargeExportVO.Business> getTotalBusinessList(List<List<TrafficChargeExportVO.Business>> businessListList) {
        List<TrafficChargeExportVO.Business> totalBusinessDataList = new LinkedList<>();
        int businessSize = businessListList.get(0).size();
        for (int i = 0; i < businessSize; i++) {
            TrafficChargeExportVO.Business business = new TrafficChargeExportVO.Business();
            business.setName(businessListList.get(0).get(i).getName());
            int finalI = i;
            business.setAmount(businessListList.stream().mapToDouble(total -> total.get(finalI).getAmount()).sum());
            business.setTotalOrderQuantity(businessListList.stream().mapToInt(total -> total.get(finalI).getTotalOrderQuantity()).sum());
            business.setTotalGoodsQuantity(businessListList.stream().mapToDouble(total -> total.get(finalI).getTotalGoodsQuantity()).sum());
            business.setProcessQuantityMap(this.mapListToMap(businessListList.stream().map(e -> e.get(finalI).getProcessQuantityMap()).collect(Collectors.toList())));
            totalBusinessDataList.add(business);
        }
        return totalBusinessDataList;
    }

    private TrafficChargeExportVO.Second getSecond(String name, List<TrafficChargeReportVO> otherProducts, List<String> firstCategories, List<SysDictData> medicineList) {
        TrafficChargeExportVO.Second second = new TrafficChargeExportVO.Second();
        second.setName(name);

        List<TrafficChargeExportVO.Second.First> firstList = new LinkedList<>();
        for (String catagory : firstCategories) {
            TrafficChargeExportVO.Second.First first = new TrafficChargeExportVO.Second.First();
            first.setName(catagory);
            List<TrafficChargeReportVO> trafficChargeReportVOS = otherProducts.stream().filter(e -> catagory.equals(e.getProcessProjectFirst())).collect(Collectors.toList());
            List<TrafficChargeExportVO.Business> businessList = new LinkedList<>();
            for (SysDictData sysDictData : medicineList) {
                String key = sysDictData.getDictValue();
                List<TrafficChargeReportVO> chargeReportVOS = trafficChargeReportVOS.stream().filter(e -> key.equals(e.getBusinessName())).collect(Collectors.toList());
                TrafficChargeExportVO.Business business = new TrafficChargeExportVO.Business();
                business.setName(key);
                business.setAmount(chargeReportVOS.stream().mapToDouble(TrafficChargeReportVO::getAmount).sum());
                business.setTotalOrderQuantity(chargeReportVOS.size());
                business.setTotalGoodsQuantity(chargeReportVOS.stream().filter(e -> "吨".equals(e.getThirdUnit())).mapToDouble(TrafficChargeReportVO::getThirdQuantity).sum());
                Map<String, BigDecimal> processQuantityMap = new LinkedHashMap<>(3);
                processQuantityMap.put("20'", BigDecimal.valueOf(chargeReportVOS.stream().
                        filter(e -> "20'".equals(e.getFirstUnit()) || "40'".equals(e.getSecondUnit())).mapToDouble(e -> e.getFirstQuantity() + e.getSecondQuantity() * 2).sum()));
                processQuantityMap.put("m3", BigDecimal.valueOf(chargeReportVOS.stream().filter(e -> "m3".equals(e.getThirdUnit())).mapToDouble(TrafficChargeReportVO::getThirdQuantity).sum()));
//                if (key.equals("消毒") || key.equals("除虫")) {
                processQuantityMap.put("m2", BigDecimal.valueOf(chargeReportVOS.stream().filter(e -> "m2".equals(e.getThirdUnit())).mapToDouble(TrafficChargeReportVO::getThirdQuantity).sum()));
//                }
                business.setProcessQuantityMap(processQuantityMap);

                businessList.add(business);
            }
            TrafficChargeExportVO.Business business = new TrafficChargeExportVO.Business();
            business.setName("业务处理量");
            business.setAmount(businessList.stream().mapToDouble(total -> total.getAmount()).sum());
            business.setTotalOrderQuantity(businessList.stream().mapToInt(total -> total.getTotalOrderQuantity()).sum());
            business.setTotalGoodsQuantity(businessList.stream().mapToDouble(total -> total.getTotalGoodsQuantity()).sum());
            business.setProcessQuantityMap(this.mapListToMap(businessList.stream().map(TrafficChargeExportVO.Business::getProcessQuantityMap).collect(Collectors.toList())));
            businessList.add(0, business);
            first.setBusinessList(businessList);

            firstList.add(first);
        }
        second.setFirstList(firstList);

        second.setBusinessList(this.getTotalBusinessList(firstList.stream().map(TrafficChargeExportVO.Second.First::getBusinessList).collect(Collectors.toList())));
        return second;
    }

    private HSSFWorkbook getTrafficChargeHSSFWorkbook(TrafficChargeExportVO trafficChargeExportVO, String fileName, List<SysDictData> medicineList) {
        //1.在内存中创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet();
        sheet.setDefaultColumnWidth(10);

        HSSFCellStyle titleCellStyle = hssfWorkbook.createCellStyle();
        HSSFFont titleFont = hssfWorkbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 16);
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeCellStyle = hssfWorkbook.createCellStyle();
        startTimeCellStyle.cloneStyleFrom(titleCellStyle);
        HSSFFont startTimeFont = hssfWorkbook.createFont();
        startTimeFont.setBold(false);
        startTimeFont.setFontName("宋体");
        startTimeFont.setFontHeightInPoints((short) 11);
        startTimeCellStyle.setFont(startTimeFont);
        startTimeCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeValueCellStyle = hssfWorkbook.createCellStyle();
        startTimeValueCellStyle.cloneStyleFrom(startTimeCellStyle);
        HSSFFont startTimeValueFont = hssfWorkbook.createFont();
        startTimeValueFont.setBold(false);
        startTimeValueFont.setFontName("宋体");
        startTimeValueFont.setFontHeightInPoints((short) 10);
        startTimeValueCellStyle.setFont(startTimeValueFont);
        startTimeValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle companyNameCellStyle = hssfWorkbook.createCellStyle();
        companyNameCellStyle.cloneStyleFrom(startTimeValueCellStyle);
        companyNameCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        companyNameCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameFont = hssfWorkbook.createFont();
        companyNameFont.setFontHeightInPoints((short) 11);
        companyNameFont.setBold(true);
        companyNameFont.setFontName("宋体");
        companyNameCellStyle.setFont(companyNameFont);

        HSSFCellStyle companyNameValueCellStyle = hssfWorkbook.createCellStyle();
        companyNameValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        companyNameValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameValueFont = hssfWorkbook.createFont();
        companyNameValueFont.setFontHeightInPoints((short) 11);
        companyNameValueFont.setBold(true);
        companyNameValueFont.setFontName("宋体");
        companyNameValueCellStyle.setFont(companyNameValueFont);

        HSSFCellStyle processMethodCellStyle = hssfWorkbook.createCellStyle();
        processMethodCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont processMethodFont = hssfWorkbook.createFont();
        processMethodFont.setFontHeightInPoints((short) 10);
        processMethodFont.setBold(true);
        processMethodFont.setFontName("宋体");
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle processMethodColorCellStyle = hssfWorkbook.createCellStyle();
        processMethodColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodColorCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        processMethodColorCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        processMethodColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle totalCellStyle = hssfWorkbook.createCellStyle();
        totalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        totalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalCellStyle.setFont(processMethodFont);
        totalCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        totalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle subTotalCellStyle = hssfWorkbook.createCellStyle();
        subTotalCellStyle.setFont(startTimeValueFont);
        subTotalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        subTotalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        subTotalCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        subTotalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle firstCellStyle = hssfWorkbook.createCellStyle();
        firstCellStyle.setFont(startTimeValueFont);
        firstCellStyle.setAlignment(HorizontalAlignment.CENTER);
        firstCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        firstCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        firstCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle yellowCellStyle = hssfWorkbook.createCellStyle();
        yellowCellStyle.setAlignment(HorizontalAlignment.CENTER);
        yellowCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        yellowCellStyle.setFont(processMethodFont);
        yellowCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //3.创建标题行
        HSSFRow titlerRow = sheet.createRow(0);
        HSSFCell fileNameCell = titlerRow.createCell(0);
        fileNameCell.setCellValue(fileName);
        fileNameCell.setCellStyle(titleCellStyle);
        int categorySize = medicineList.size();
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 7 + categorySize * 7);
        sheet.addMergedRegion(region);
        HSSFCell startDateCell = titlerRow.createCell(8 + categorySize * 7);
        startDateCell.setCellValue("起始日期");
        startDateCell.setCellStyle(startTimeCellStyle);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        HSSFCell startDateValueCell = titlerRow.createCell(9 + categorySize * 7);
        startDateValueCell.setCellValue(simpleDateFormat.format(trafficChargeExportVO.getCreateTimeStart()));
        startDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow2 = sheet.createRow(1);
        HSSFCell companyNameCell = titlerRow2.createCell(0);
        companyNameCell.setCellValue("分公司名称（盖章）：");
        companyNameCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress companyRegion = new CellRangeAddress(1, 1, 0, 2);
        sheet.addMergedRegion(companyRegion);
        HSSFCell companyNameValueCell = titlerRow2.createCell(3);
        companyNameValueCell.setCellValue(trafficChargeExportVO.getCompanyName());
        companyNameValueCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress companyValueRegion = new CellRangeAddress(1, 1, 3, 4);
        sheet.addMergedRegion(companyValueRegion);
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy年MM月");
        HSSFCell createTimeCell = titlerRow2.createCell(8);
        createTimeCell.setCellValue(simpleDateFormat2.format(new Date()));
        createTimeCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress createTimeRegion = new CellRangeAddress(1, 1, 8, 20);
        sheet.addMergedRegion(createTimeRegion);
        HSSFCell endDateCell = titlerRow2.createCell(8 + categorySize * 7);
        endDateCell.setCellValue("截止日期");
        endDateCell.setCellStyle(startTimeCellStyle);
        HSSFCell endDateValueCell = titlerRow2.createCell(9 + categorySize * 7);
        endDateValueCell.setCellValue(simpleDateFormat.format(trafficChargeExportVO.getCreateTimeEnd()));
        endDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow3 = sheet.createRow(2);
        HSSFCell medincineCell = titlerRow3.createCell(0);
        medincineCell.setCellValue("处理方法");
        medincineCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress medicineRegion = new CellRangeAddress(2, 4, 0, 1);
        sheet.addMergedRegion(medicineRegion);
        List<TrafficChargeExportVO.Business> businessList = trafficChargeExportVO.getBusinessDataList();
        int fci = 2;
        for (TrafficChargeExportVO.Business business : businessList) {
            HSSFCell businessCell = titlerRow3.createCell(fci);
            businessCell.setCellValue(business.getName());
            businessCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress fcRegion;
            if ("业务处理量".equals(business.getName()) || "熏蒸".equals(business.getName())) {
                fcRegion = new CellRangeAddress(2, 2, fci, fci + 5);
                sheet.addMergedRegion(fcRegion);
                fci = fci + 6;
            } else {
                fcRegion = new CellRangeAddress(2, 2, fci, fci + 6);
                sheet.addMergedRegion(fcRegion);
                fci = fci + 7;
            }
        }

        HSSFRow titlerRow4 = sheet.createRow(3);
        fci = 2;
        for (TrafficChargeExportVO.Business business : businessList) {
            HSSFCell batchCell = titlerRow4.createCell(fci);
            batchCell.setCellValue("批次");
            batchCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress orderRegion = new CellRangeAddress(3, 4, fci, fci);
            sheet.addMergedRegion(orderRegion);
            HSSFCell quantityCell = titlerRow4.createCell(++fci);
            quantityCell.setCellValue("处理量");
            quantityCell.setCellStyle(processMethodColorCellStyle);
            if ("熏蒸".equals(business.getName())) {
                CellRangeAddress quantityRegion = new CellRangeAddress(3, 3, fci, fci + 1);
                sheet.addMergedRegion(quantityRegion);
                fci = fci + 2;
            } else {
                CellRangeAddress quantityRegion = new CellRangeAddress(3, 3, fci, fci + 2);
                sheet.addMergedRegion(quantityRegion);
                fci = fci + 3;
            }
            HSSFCell goodsCell = titlerRow4.createCell(fci);
            goodsCell.setCellValue("对应货物量");
            goodsCell.setCellStyle(processMethodColorCellStyle);
            if (!"业务处理量".equals(business.getName())) {
                HSSFCell biaozhunCell = titlerRow4.createCell(++fci);
                biaozhunCell.setCellValue("收费标准(元）");
                biaozhunCell.setCellStyle(processMethodCellStyle);
                CellRangeAddress chargeRegion = new CellRangeAddress(3, 4, fci, fci);
                sheet.addMergedRegion(chargeRegion);
            }
            HSSFCell moneyCell = titlerRow4.createCell(++fci);
            moneyCell.setCellValue("金额");
            moneyCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress amountRegion = new CellRangeAddress(3, 4, fci, fci);
            sheet.addMergedRegion(amountRegion);
            ++fci;
        }

        HSSFRow titlerRow5 = sheet.createRow(4);
        fci = 3;
        boolean firstCol = true;
        for (TrafficChargeExportVO.Business business : businessList) {
            HSSFCell biaoguiCell = titlerRow5.createCell(fci);
            biaoguiCell.setCellValue("20'标柜");
            biaoguiCell.setCellStyle(processMethodColorCellStyle);
            HSSFCell volumnCell = titlerRow5.createCell(++fci);
            volumnCell.setCellValue("m3");
            volumnCell.setCellStyle(processMethodColorCellStyle);
            if (!"熏蒸".equals(business.getName())) {
                HSSFCell areaCell = titlerRow5.createCell(++fci);
                areaCell.setCellValue("m2");
                areaCell.setCellStyle(processMethodColorCellStyle);
            }
            HSSFCell dunCell = titlerRow5.createCell(++fci);
            dunCell.setCellValue("(T)");
            dunCell.setCellStyle(processMethodColorCellStyle);
            if (firstCol) {
                fci = fci + 3;
                firstCol = false;
            } else {
                fci = fci + 4;
            }
        }

        List<TrafficChargeExportVO.Second> secondList = trafficChargeExportVO.getSecondList();
        int ri = 5;
        for (TrafficChargeExportVO.Second second : secondList) {
            List<TrafficChargeExportVO.Second.First> firstList = second.getFirstList();
            int firstIndex = 0;
            for (TrafficChargeExportVO.Second.First first : firstList) {
                if (firstIndex == 0) {
                    firstIndex = ri;
                }
                int ci = 1;
                HSSFRow row = sheet.createRow(ri);
                if (firstList.size() != 1) {
                    HSSFCell firstCell = row.createCell(ci);
                    firstCell.setCellValue(first.getName());
                    firstCell.setCellStyle(firstCellStyle);
                } else {
                    CellRangeAddress amountRegion = new CellRangeAddress(ri, ri, 0, 1);
                    sheet.addMergedRegion(amountRegion);
                }
                List<TrafficChargeExportVO.Business> businesses = first.getBusinessList();
                this.padValue(row, ci, businesses, startTimeValueCellStyle);

                ++ri;
            }
            HSSFRow row = sheet.getRow(firstIndex);
            HSSFCell secondCell = row.createCell(0);
            secondCell.setCellValue(second.getName());
            secondCell.setCellStyle(startTimeValueCellStyle);
            if (firstList.size() > 1) {
                CellRangeAddress amountRegion = new CellRangeAddress(firstIndex, ri - 1, 0, 0);
                sheet.addMergedRegion(amountRegion);

                HSSFRow subTotalRow = sheet.createRow(ri);
                HSSFCell subTotalCell = subTotalRow.createCell(0);
                subTotalCell.setCellValue("小计");
                subTotalCell.setCellStyle(subTotalCellStyle);
                CellRangeAddress subTotalRegion = new CellRangeAddress(ri, ri, 0, 1);
                sheet.addMergedRegion(subTotalRegion);
                this.padValue(subTotalRow, 1, second.getBusinessList(), totalCellStyle);

                ++ri;
            }
        }

        HSSFRow totalRow = sheet.createRow(ri);
        HSSFCell totalCell = totalRow.createCell(0);
        totalCell.setCellValue("合计");
        totalCell.setCellStyle(yellowCellStyle);
        CellRangeAddress totalRegion = new CellRangeAddress(ri, ri, 0, 1);
        sheet.addMergedRegion(totalRegion);
        this.padValue(totalRow, 1, trafficChargeExportVO.getBusinessDataList(), yellowCellStyle);

        HSSFRow footRow = sheet.createRow(++ri);
        HSSFCell personNameCell = footRow.createCell(0);
        personNameCell.setCellValue("填报人（签名）：");
        personNameCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress footRegion = new CellRangeAddress(ri, ri, 0, 1);
        sheet.addMergedRegion(footRegion);
        HSSFCell informantCell = footRow.createCell(2);
        informantCell.setCellValue(trafficChargeExportVO.getInformant());
        informantCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress informantRegion = new CellRangeAddress(ri, ri, 2, 3);
        sheet.addMergedRegion(informantRegion);

        HSSFCell leaderCell = footRow.createCell(categorySize * 7);
        leaderCell.setCellValue("分公司负责人（签名）：");
        leaderCell.setCellStyle(companyNameCellStyle);

        CellRangeAddress leaderRegion = new CellRangeAddress(ri, ri, categorySize * 7, categorySize * 7 + 2);
        sheet.addMergedRegion(leaderRegion);

        return hssfWorkbook;
    }

    private void padValue(HSSFRow row, int ci, List<TrafficChargeExportVO.Business> businesses, HSSFCellStyle startTimeValueCellStyle) {
        for (TrafficChargeExportVO.Business business : businesses) {
            HSSFCell orderQuantityCell = row.createCell(++ci);
            orderQuantityCell.setCellValue(business.getTotalOrderQuantity());
            orderQuantityCell.setCellStyle(startTimeValueCellStyle);
            Map<String, BigDecimal> processQuantityMap = business.getProcessQuantityMap();
            HSSFCell biaoguiCell = row.createCell(++ci);
            biaoguiCell.setCellValue(processQuantityMap.get("20'").doubleValue());
            biaoguiCell.setCellStyle(startTimeValueCellStyle);
            HSSFCell volumeCell = row.createCell(++ci);
            volumeCell.setCellValue(processQuantityMap.get("m3").doubleValue());
            volumeCell.setCellStyle(startTimeValueCellStyle);
            if (!"熏蒸".equals(business.getName())) {
                HSSFCell areaCell = row.createCell(++ci);
                areaCell.setCellValue(processQuantityMap.get("m2").doubleValue());
                areaCell.setCellStyle(startTimeValueCellStyle);
            }
            HSSFCell goodsQuantityCell = row.createCell(++ci);
            goodsQuantityCell.setCellValue(business.getTotalGoodsQuantity());
            goodsQuantityCell.setCellStyle(startTimeValueCellStyle);
            if (!"业务处理量".equals(business.getName())) {
                HSSFCell cell = row.createCell(++ci);
                cell.setCellValue("");
                cell.setCellStyle(startTimeValueCellStyle);
            }
            HSSFCell cell = row.createCell(++ci);
            cell.setCellValue(business.getAmount());
            cell.setCellStyle(startTimeValueCellStyle);
        }
    }

    private HSSFWorkbook exportDoseReport(DoseExportVO doseExportVO, List<SysDictData> projectCategories, String fileName) {
        //1.在内存中创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet();
        sheet.setDefaultColumnWidth(10);

        HSSFCellStyle titleCellStyle = hssfWorkbook.createCellStyle();
        HSSFFont titleFont = hssfWorkbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 16);
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeCellStyle = hssfWorkbook.createCellStyle();
        startTimeCellStyle.cloneStyleFrom(titleCellStyle);
        HSSFFont startTimeFont = hssfWorkbook.createFont();
        startTimeFont.setBold(false);
        startTimeFont.setFontName("宋体");
        startTimeFont.setFontHeightInPoints((short) 11);
        startTimeCellStyle.setFont(startTimeFont);
        startTimeCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeValueCellStyle = hssfWorkbook.createCellStyle();
        startTimeValueCellStyle.cloneStyleFrom(startTimeCellStyle);
        HSSFFont startTimeValueFont = hssfWorkbook.createFont();
        startTimeValueFont.setBold(false);
        startTimeValueFont.setFontName("宋体");
        startTimeValueFont.setFontHeightInPoints((short) 10);
        startTimeValueCellStyle.setFont(startTimeValueFont);
        startTimeValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle companyNameCellStyle = hssfWorkbook.createCellStyle();
        companyNameCellStyle.cloneStyleFrom(startTimeValueCellStyle);
        companyNameCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        companyNameCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameFont = hssfWorkbook.createFont();
        companyNameFont.setFontHeightInPoints((short) 11);
        companyNameFont.setBold(true);
        companyNameFont.setFontName("宋体");
        companyNameCellStyle.setFont(companyNameFont);

        HSSFCellStyle companyNameValueCellStyle = hssfWorkbook.createCellStyle();
        companyNameValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        companyNameValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameValueFont = hssfWorkbook.createFont();
        companyNameValueFont.setFontHeightInPoints((short) 11);
        companyNameValueFont.setBold(true);
        companyNameValueFont.setFontName("宋体");
        companyNameValueCellStyle.setFont(companyNameValueFont);

        HSSFCellStyle processMethodCellStyle = hssfWorkbook.createCellStyle();
        processMethodCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont processMethodFont = hssfWorkbook.createFont();
        processMethodFont.setFontHeightInPoints((short) 10);
        processMethodFont.setBold(true);
        processMethodFont.setFontName("宋体");
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle processMethodColorCellStyle = hssfWorkbook.createCellStyle();
        processMethodColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodColorCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        processMethodColorCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        processMethodColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle totalCellStyle = hssfWorkbook.createCellStyle();
        totalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        totalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalCellStyle.setFont(processMethodFont);
        totalCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        totalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle subTotalCellStyle = hssfWorkbook.createCellStyle();
        subTotalCellStyle.setFont(startTimeValueFont);
        subTotalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        subTotalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        subTotalCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        subTotalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //3.创建标题行
        HSSFRow titlerRow = sheet.createRow(0);
        HSSFCell fileNameCell = titlerRow.createCell(0);
        fileNameCell.setCellValue(fileName);
        fileNameCell.setCellStyle(titleCellStyle);
        int categorySize = projectCategories.size();
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4 + categorySize * 2);
        sheet.addMergedRegion(region);
        HSSFCell startDateCell = titlerRow.createCell(5 + categorySize * 2);
        startDateCell.setCellValue("起始日期");
        startDateCell.setCellStyle(startTimeCellStyle);
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        HSSFCell startDateValueCell = titlerRow.createCell(6 + categorySize * 2);
        startDateValueCell.setCellValue(simpleDateFormat.format(doseExportVO.getCreateTimeStart()));
        startDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow2 = sheet.createRow(1);
        HSSFCell companyNameCell = titlerRow2.createCell(0);
        companyNameCell.setCellValue("分公司名称（盖章）：");
        companyNameCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress companyRegion = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(companyRegion);
        HSSFCell companyNameValueCell = titlerRow2.createCell(2);
        companyNameValueCell.setCellValue(doseExportVO.getCompanyName());
        companyNameValueCell.setCellStyle(companyNameValueCellStyle);
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy年MM月");
        HSSFCell createTimeCell = titlerRow2.createCell(6);
        createTimeCell.setCellValue(simpleDateFormat2.format(new Date()));
        createTimeCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress createTimeRegion = new CellRangeAddress(1, 1, 6, 9);
        sheet.addMergedRegion(createTimeRegion);
        HSSFCell endDateCell = titlerRow2.createCell(5 + categorySize * 2);
        endDateCell.setCellValue("截止日期");
        endDateCell.setCellStyle(startTimeCellStyle);
        HSSFCell endDateValueCell = titlerRow2.createCell(6 + categorySize * 2);
        endDateValueCell.setCellValue(simpleDateFormat.format(doseExportVO.getCreateTimeEnd()));
        endDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow3 = sheet.createRow(2);
        HSSFCell medincineCell = titlerRow3.createCell(0);
        medincineCell.setCellValue("药剂名称");
        medincineCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress medicineRegion = new CellRangeAddress(2, 4, 0, 1);
        sheet.addMergedRegion(medicineRegion);
        HSSFCell unitCell = titlerRow3.createCell(2);
        unitCell.setCellValue("计量单位");
        unitCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress unitRegion = new CellRangeAddress(2, 4, 2, 2);
        sheet.addMergedRegion(unitRegion);
        HSSFCell totalMedicineCell = titlerRow3.createCell(3);
        totalMedicineCell.setCellValue("用药总量合计");
        totalMedicineCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress quantityRegion = new CellRangeAddress(2, 4, 3, 3);
        sheet.addMergedRegion(quantityRegion);
        int ci = 4;
        for (SysDictData dictData : projectCategories) {
            HSSFCell firstCell = titlerRow3.createCell(ci);
            firstCell.setCellValue(dictData.getDictLabel());
            firstCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress processObjectRegion = new CellRangeAddress(2, 2, ci, "其他".equals(dictData.getDictLabel()) ? ci + 2 : ci + 1);
            sheet.addMergedRegion(processObjectRegion);
            ci = ci + 2;
        }

        HSSFRow titlerRow4 = sheet.createRow(3);
        ci = 4;
        for (SysDictData dictData : projectCategories) {
            HSSFCell subTotalCell = titlerRow4.createCell(ci);
            subTotalCell.setCellValue("用药量小计");
            subTotalCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress processObjectRegion = new CellRangeAddress(3, 4, ci, ci);
            sheet.addMergedRegion(processObjectRegion);
            HSSFCell quantityCell = titlerRow4.createCell(++ci);
            quantityCell.setCellValue("对应处理量");
            quantityCell.setCellStyle(processMethodColorCellStyle);
            if ("其他".equals(dictData.getDictLabel())) {
                HSSFCell waterCell = titlerRow4.createCell(++ci);
                waterCell.setCellValue("压舱水重量");
                waterCell.setCellStyle(processMethodColorCellStyle);
            }
            ++ci;
        }
        HSSFRow titlerRow5 = sheet.createRow(4);
        ci = 5;
        for (SysDictData dictData : projectCategories) {
            HSSFCell processObjectUnitCell = titlerRow5.createCell(ci);
            processObjectUnitCell.setCellValue(this.getProcessObjectUnit(ProcessObjectUnitEnum.getUnitSet(dictData.getDictLabel())));
            processObjectUnitCell.setCellStyle(processMethodColorCellStyle);
            if ("其他".equals(dictData.getDictLabel())) {
                HSSFCell dunCell = titlerRow5.createCell(ci + 1);
                dunCell.setCellValue("(吨)");
                dunCell.setCellStyle(processMethodColorCellStyle);
            }
            ci = ci + 2;
        }

        List<DoseExportVO.BusinessVO> businessVOList = doseExportVO.getBusinessVOList();
        int ri = 5;
        for (DoseExportVO.BusinessVO businessVO : businessVOList) {
            List<DoseExportVO.BusinessVO.MedicineVO> medicineVOList = businessVO.getMedicineVOList();
            int medicineRowIndex = 0;
            for (DoseExportVO.BusinessVO.MedicineVO medicineVO : medicineVOList) {
                if (medicineRowIndex == 0) {
                    medicineRowIndex = ri;
                }
                HSSFRow medicineRow = sheet.createRow(ri);
                int medicineCi = 1;
                HSSFCell usedElixirCell = medicineRow.createCell(medicineCi);
                usedElixirCell.setCellValue(medicineVO.getUsedElixir());
                usedElixirCell.setCellStyle(startTimeValueCellStyle);
                HSSFCell quantityUnitCell = medicineRow.createCell(++medicineCi);
                quantityUnitCell.setCellValue(medicineVO.getUnit());
                quantityUnitCell.setCellStyle(startTimeValueCellStyle);
                HSSFCell totalMedicationCell = medicineRow.createCell(++medicineCi);
                totalMedicationCell.setCellValue(medicineVO.getTotalMedication());
                totalMedicationCell.setCellStyle(totalCellStyle);
                List<DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO> projectFirstVOList = medicineVO.getProjectFirstVOList();
                for (DoseExportVO.BusinessVO.MedicineVO.ProjectFirstVO projectFirstVO : projectFirstVOList) {
                    HSSFCell medicationCell = medicineRow.createCell(++medicineCi);
                    medicationCell.setCellValue(projectFirstVO.getTotalMedication());
                    medicationCell.setCellStyle(subTotalCellStyle);
                    HSSFCell processQuantityCell = medicineRow.createCell(++medicineCi);
                    processQuantityCell.setCellValue(projectFirstVO.getTotalProcessQuantity());
                    processQuantityCell.setCellStyle(startTimeValueCellStyle);
                    if (projectFirstVO.getBallastWaterWeight() != null) {
                        HSSFCell waterWeightCell = medicineRow.createCell(++medicineCi);
                        waterWeightCell.setCellValue(projectFirstVO.getBallastWaterWeight());
                        waterWeightCell.setCellStyle(startTimeValueCellStyle);
                    }
                }
                ri++;
            }
            HSSFRow firstRow = sheet.getRow(medicineRowIndex);
            HSSFCell businessNameCell = firstRow.createCell(0);
            businessNameCell.setCellValue(businessVO.getBusinessName());
            businessNameCell.setCellStyle(startTimeValueCellStyle);
            if (medicineRowIndex != ri - 1) {
                CellRangeAddress businessNameRegion = new CellRangeAddress(medicineRowIndex, ri - 1, 0, 0);
                sheet.addMergedRegion(businessNameRegion);
            }
        }
        HSSFRow footRow = sheet.createRow(++ri);
        HSSFCell personNameCell = footRow.createCell(0);
        personNameCell.setCellValue("填报人（签名）：");
        personNameCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress personNameRegion = new CellRangeAddress(ri, ri, 0, 1);
        sheet.addMergedRegion(personNameRegion);
        HSSFCell informantCell = footRow.createCell(2);
        informantCell.setCellValue(doseExportVO.getInformant());
        informantCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress personValueRegion = new CellRangeAddress(ri, ri, 2, 3);
        sheet.addMergedRegion(personValueRegion);
        HSSFCell leaderCell = footRow.createCell(categorySize * 2);
        leaderCell.setCellValue("分公司负责人（签名）：");
        leaderCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress leaderRegion = new CellRangeAddress(ri, ri, categorySize * 2, categorySize * 2 + 2);
        sheet.addMergedRegion(leaderRegion);

        return hssfWorkbook;
    }

    private String getProcessObjectUnit(Set<String> unitSet) {
        if (CollectionUtils.isEmpty(unitSet)) {
            return "";
        }
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("(");
        for (String unit : unitSet) {
            stringBuffer.append(unit);
            stringBuffer.append("或");
        }
        stringBuffer.delete(stringBuffer.toString().length() - 1, stringBuffer.toString().length());
        stringBuffer.append(")");
        return stringBuffer.toString();
    }

    private HSSFWorkbook exportBusinessExcel(BusinessVolumeExportVO businessVolumeExportVO, List<SysDictData> medicineList, String fileName) throws IOException {
        //1.在内存中创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet();
        sheet.setDefaultColumnWidth(15);

        HSSFCellStyle titleCellStyle = hssfWorkbook.createCellStyle();
        HSSFFont titleFont = hssfWorkbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 16);
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeCellStyle = hssfWorkbook.createCellStyle();
        startTimeCellStyle.cloneStyleFrom(titleCellStyle);
        HSSFFont startTimeFont = hssfWorkbook.createFont();
        startTimeFont.setBold(false);
        startTimeFont.setFontName("宋体");
        startTimeFont.setFontHeightInPoints((short) 11);
        startTimeCellStyle.setFont(startTimeFont);
        startTimeCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeValueCellStyle = hssfWorkbook.createCellStyle();
        startTimeValueCellStyle.cloneStyleFrom(startTimeCellStyle);
        HSSFFont startTimeValueFont = hssfWorkbook.createFont();
        startTimeValueFont.setBold(false);
        startTimeValueFont.setFontName("宋体");
        startTimeValueFont.setFontHeightInPoints((short) 10);
        startTimeValueCellStyle.setFont(startTimeValueFont);
        startTimeValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle companyNameCellStyle = hssfWorkbook.createCellStyle();
        companyNameCellStyle.cloneStyleFrom(startTimeValueCellStyle);
        companyNameCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        companyNameCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameFont = hssfWorkbook.createFont();
        companyNameFont.setFontHeightInPoints((short) 11);
        companyNameFont.setBold(true);
        companyNameFont.setFontName("宋体");
        companyNameCellStyle.setFont(companyNameFont);

        HSSFCellStyle companyNameValueCellStyle = hssfWorkbook.createCellStyle();
        companyNameValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        companyNameValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameValueFont = hssfWorkbook.createFont();
        companyNameValueFont.setFontHeightInPoints((short) 11);
        companyNameValueFont.setBold(true);
        companyNameValueFont.setFontName("宋体");
        companyNameValueCellStyle.setFont(companyNameValueFont);

        HSSFCellStyle processMethodCellStyle = hssfWorkbook.createCellStyle();
        processMethodCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont processMethodFont = hssfWorkbook.createFont();
        processMethodFont.setFontHeightInPoints((short) 10);
        processMethodFont.setBold(true);
        processMethodFont.setFontName("宋体");
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle processMethodColorCellStyle = hssfWorkbook.createCellStyle();
        processMethodColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodColorCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        processMethodColorCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        processMethodColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle totalCellStyle = hssfWorkbook.createCellStyle();
        totalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        totalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalCellStyle.setFont(processMethodFont);
        totalCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        totalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //3.创建标题行
        HSSFRow titlerRow = sheet.createRow(0);
        HSSFCell fileNameCell = titlerRow.createCell(0);
        fileNameCell.setCellValue(fileName);

        int businessSize = medicineList.size();
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1 + businessSize * 3);
        sheet.addMergedRegion(region);
        fileNameCell.setCellStyle(titleCellStyle);

        HSSFCell startTimeCell = titlerRow.createCell(2 + businessSize * 3);
        startTimeCell.setCellValue("起始日期");
        startTimeCell.setCellStyle(startTimeCellStyle);

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        HSSFCell startTimeValueCell = titlerRow.createCell(3 + businessSize * 3);
        startTimeValueCell.setCellValue(simpleDateFormat.format(businessVolumeExportVO.getCreateTimeStart()));
        startTimeValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow2 = sheet.createRow(1);
        HSSFCell companyCell = titlerRow2.createCell(0);
        companyCell.setCellValue("分公司名称（盖章）：");
        companyCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress companyRegion = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(companyRegion);
        HSSFCell companyNameCell = titlerRow2.createCell(2);
        companyNameCell.setCellValue(businessVolumeExportVO.getCompanyName());
        companyNameCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress companyNameRegion = new CellRangeAddress(1, 1, 2, 3);
        sheet.addMergedRegion(companyNameRegion);
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy年MM月");
        HSSFCell createDateCell = titlerRow2.createCell(4);
        createDateCell.setCellValue(simpleDateFormat2.format(new Date()));
        createDateCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress createTimeRegion = new CellRangeAddress(1, 1, 4, 6);
        sheet.addMergedRegion(createTimeRegion);
        HSSFCell endTimeCell = titlerRow2.createCell(2 + businessSize * 3);
        endTimeCell.setCellValue("截止日期");
        endTimeCell.setCellStyle(startTimeCellStyle);
        HSSFCell endTimeValueCell = titlerRow2.createCell(3 + businessSize * 3);
        endTimeValueCell.setCellValue(simpleDateFormat.format(businessVolumeExportVO.getCreateTimeEnd()));
        endTimeValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow3 = sheet.createRow(2);
        HSSFCell processMethodCell = titlerRow3.createCell(0);
        processMethodCell.setCellValue("处理方法");
        processMethodCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress processProjectRegion = new CellRangeAddress(2, 4, 0, 1);
        sheet.addMergedRegion(processProjectRegion);
        int ci = 2;
        for (SysDictData dictData : medicineList) {
            HSSFCell businessCell = titlerRow3.createCell(ci);
            businessCell.setCellValue(dictData.getDictLabel());
            businessCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress processObjectRegion = new CellRangeAddress(2, 2, ci, ci + 2);
            sheet.addMergedRegion(processObjectRegion);
            ci = ci + 3;
        }

        HSSFRow titlerRow4 = sheet.createRow(3);
        ci = 2;
        for (SysDictData dictData : medicineList) {
            HSSFCell batchCell = titlerRow4.createCell(ci);
            batchCell.setCellValue("批次");
            batchCell.setCellStyle(processMethodColorCellStyle);
            CellRangeAddress processObjectRegion = new CellRangeAddress(3, 4, ci, ci);
            sheet.addMergedRegion(processObjectRegion);
            HSSFCell quantityCell = titlerRow4.createCell(++ci);
            quantityCell.setCellValue("处理量");
            quantityCell.setCellStyle(processMethodColorCellStyle);
            HSSFCell goodsCell = titlerRow4.createCell(++ci);
            goodsCell.setCellValue("对应货物量");
            goodsCell.setCellStyle(processMethodColorCellStyle);
            ++ci;
        }
        HSSFRow titlerRow5 = sheet.createRow(4);
        ci = 3;
        for (SysDictData dictData : medicineList) {
            HSSFCell unitCell = titlerRow5.createCell(ci);
            unitCell.setCellValue(dictData.getDictLabel().equals("熏蒸") ? "(20'或m3)" : "(20'或m2,m3)");
            unitCell.setCellStyle(processMethodColorCellStyle);
            HSSFCell dunCell = titlerRow5.createCell(++ci);
            dunCell.setCellValue("(T)");
            dunCell.setCellStyle(processMethodColorCellStyle);
            ci = ci + 2;
        }
        List<BusinessVolumeExportVO.ProjectFirst> projectFirstList = businessVolumeExportVO.getProjectFirstList();
        AtomicInteger ri = new AtomicInteger(5);
        projectFirstList.stream().forEach(first -> {
            List<BusinessVolumeExportVO.ProjectFirst.ProjectSecond> secondList = first.getProjectSecondList();
            AtomicInteger firstKindIndex = new AtomicInteger(0);
            secondList.stream().forEach(second -> {
                if (firstKindIndex.get() == 0) {
                    firstKindIndex.set(ri.get());
                }
                HSSFRow row = sheet.createRow(ri.get());
                HSSFCell secondCell = row.createCell(1);
                secondCell.setCellValue(second.getName());
                secondCell.setCellStyle(startTimeValueCellStyle);
                Map<String, BusinessVolumeExportVO.BusinessData> businessDataMap = second.getBusinessDataMap();
                int colIndex = 2;
                for (SysDictData dictData : medicineList) {
                    BusinessVolumeExportVO.BusinessData businessData = businessDataMap.get(dictData.getDictLabel());
                    HSSFCell totalOrderQuantityCell = row.createCell(colIndex);
                    totalOrderQuantityCell.setCellValue(businessData.getTotalOrderQuantity());
                    totalOrderQuantityCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell totalProcessQuantityCell = row.createCell(++colIndex);
                    totalProcessQuantityCell.setCellValue(businessData.getTotalProcessQuantity());
                    totalProcessQuantityCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell totalGoodsQuantityCell = row.createCell(++colIndex);
                    totalGoodsQuantityCell.setCellValue(businessData.getTotalGoodsQuantity());
                    totalGoodsQuantityCell.setCellStyle(startTimeValueCellStyle);
                    ++colIndex;
                }
                ri.incrementAndGet();
            });
            HSSFCell firstCell = sheet.getRow(firstKindIndex.get()).createCell(0);
            firstCell.setCellValue(first.getName());
            firstCell.setCellStyle(startTimeValueCellStyle);
            CellRangeAddress firstKindRegion = new CellRangeAddress(firstKindIndex.get(), ri.get(), 0, 0);
            sheet.addMergedRegion(firstKindRegion);

            HSSFRow totalRow = sheet.createRow(ri.get());
            HSSFCell totalCell = totalRow.createCell(1);
            totalCell.setCellValue("合计");
            totalCell.setCellStyle(totalCellStyle);
            Map<String, BusinessVolumeExportVO.BusinessData> businessDataMap = first.getBusinessDataMap();
            int colIndex = 2;
            for (SysDictData dictData : medicineList) {
                BusinessVolumeExportVO.BusinessData businessData = businessDataMap.get(dictData.getDictLabel());
                HSSFCell totalOrderCell = totalRow.createCell(colIndex);
                totalOrderCell.setCellValue(businessData.getTotalOrderQuantity());
                totalOrderCell.setCellStyle(totalCellStyle);
                HSSFCell totalProcessCell = totalRow.createCell(++colIndex);
                totalProcessCell.setCellValue(businessData.getTotalProcessQuantity());
                totalProcessCell.setCellStyle(totalCellStyle);
                HSSFCell totalGoodsCell = totalRow.createCell(++colIndex);
                totalGoodsCell.setCellValue(businessData.getTotalGoodsQuantity());
                totalGoodsCell.setCellStyle(totalCellStyle);
                ++colIndex;
            }
            ri.incrementAndGet();
        });
        ri.incrementAndGet();

        HSSFRow footRow = sheet.createRow(ri.get());
        HSSFCell informantCell = footRow.createCell(0);
        informantCell.setCellValue("填报人（签名）：");
        informantCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress informantRegion = new CellRangeAddress(ri.get(), ri.get(), 0, 1);
        sheet.addMergedRegion(informantRegion);
        footRow.createCell(2).setCellValue(businessVolumeExportVO.getInformant());
        CellRangeAddress informantRegion2 = new CellRangeAddress(ri.get(), ri.get(), 2, 3);
        sheet.addMergedRegion(informantRegion2);

        HSSFCell leaderCell = footRow.createCell(businessSize * 3 - 2);
        leaderCell.setCellValue("分公司负责人（签名）：");
        leaderCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress leaderRegion = new CellRangeAddress(ri.get(), ri.get(), businessSize * 3 - 2, businessSize * 3 - 1);
        sheet.addMergedRegion(leaderRegion);

        return hssfWorkbook;
    }

    private void writeResponse(HSSFWorkbook hssfWorkbook, HttpServletResponse response, String fileName) throws IOException {
        //6.获取输出流对象
        ServletOutputStream outputStream = response.getOutputStream();
        //设置响应
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        response.setContentType("application/vnd.ms-excel");

        hssfWorkbook.write(outputStream);
        // 刷新流
        outputStream.flush();
        // 关闭流
        outputStream.close();
    }

    private HSSFWorkbook exportExcel(DisinfectionReportExportVO disinfectionReportExportVO, DisinfectionReportDTO disinfectionReportDTO, String fileName) throws IOException {
        //1.在内存中创建一个excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建工作簿
        HSSFSheet sheet = hssfWorkbook.createSheet();
        sheet.setDefaultColumnWidth(15);

        HSSFCellStyle titleCellStyle = hssfWorkbook.createCellStyle();
        HSSFFont titleFont = hssfWorkbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 14);
        titleCellStyle.setFont(titleFont);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeCellStyle = hssfWorkbook.createCellStyle();
        startTimeCellStyle.cloneStyleFrom(titleCellStyle);
        HSSFFont startTimeFont = hssfWorkbook.createFont();
        startTimeFont.setBold(false);
        startTimeFont.setFontName("宋体");
        startTimeFont.setFontHeightInPoints((short) 11);
        startTimeCellStyle.setFont(startTimeFont);
        startTimeCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle startTimeValueCellStyle = hssfWorkbook.createCellStyle();
        startTimeValueCellStyle.cloneStyleFrom(startTimeCellStyle);
        HSSFFont startTimeValueFont = hssfWorkbook.createFont();
        startTimeValueFont.setBold(false);
        startTimeValueFont.setFontName("宋体");
        startTimeValueFont.setFontHeightInPoints((short) 10);
        startTimeValueCellStyle.setFont(startTimeValueFont);
        startTimeValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        startTimeValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle companyNameCellStyle = hssfWorkbook.createCellStyle();
        companyNameCellStyle.cloneStyleFrom(startTimeValueCellStyle);
        companyNameCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        companyNameCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameFont = hssfWorkbook.createFont();
        companyNameFont.setFontHeightInPoints((short) 11);
        companyNameFont.setBold(true);
        companyNameFont.setFontName("宋体");
        companyNameCellStyle.setFont(companyNameFont);

        HSSFCellStyle companyNameValueCellStyle = hssfWorkbook.createCellStyle();
        companyNameValueCellStyle.setAlignment(HorizontalAlignment.CENTER);
        companyNameValueCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont companyNameValueFont = hssfWorkbook.createFont();
        companyNameValueFont.setFontHeightInPoints((short) 11);
        companyNameValueFont.setBold(true);
        companyNameValueFont.setFontName("宋体");
        companyNameValueCellStyle.setFont(companyNameValueFont);

        HSSFCellStyle processMethodCellStyle = hssfWorkbook.createCellStyle();
        processMethodCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont processMethodFont = hssfWorkbook.createFont();
        processMethodFont.setFontHeightInPoints((short) 10);
        processMethodFont.setBold(true);
        processMethodFont.setFontName("宋体");
        processMethodCellStyle.setFont(processMethodFont);

        HSSFCellStyle processMethodColorCellStyle = hssfWorkbook.createCellStyle();
        processMethodColorCellStyle.setAlignment(HorizontalAlignment.CENTER);
        processMethodColorCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        processMethodColorCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        processMethodColorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        processMethodColorCellStyle.setFont(processMethodFont);

        HSSFCellStyle totalCellStyle = hssfWorkbook.createCellStyle();
        totalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        totalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        totalCellStyle.setFont(processMethodFont);
        totalCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        totalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFCellStyle subTotalCellStyle = hssfWorkbook.createCellStyle();
        subTotalCellStyle.setFont(startTimeValueFont);
        subTotalCellStyle.setAlignment(HorizontalAlignment.CENTER);
        subTotalCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        subTotalCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        subTotalCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        //3.创建标题行
        HSSFRow titlerRow = sheet.createRow(0);
        HSSFCell titalCell = titlerRow.createCell(0);
        titalCell.setCellValue(fileName);
        titalCell.setCellStyle(titleCellStyle);
        Map<String, BigDecimal> totalMedicationMap = disinfectionReportExportVO.getProcessProjectFirstList().get(0).getTotalMedicationMap();
        int medicineSize = totalMedicationMap.size();
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5 + medicineSize);
        sheet.addMergedRegion(region);
        HSSFCell startDateCell = titlerRow.createCell(6 + medicineSize);
        startDateCell.setCellValue("起始日期");
        startDateCell.setCellStyle(startTimeCellStyle);

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        HSSFCell startDateValueCell = titlerRow.createCell(7 + medicineSize);
        startDateValueCell.setCellValue(simpleDateFormat.format(disinfectionReportDTO.getCreateTimeStart()));
        startDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow2 = sheet.createRow(1);
        HSSFCell companyNameCell = titlerRow2.createCell(0);
        companyNameCell.setCellValue("分公司名称：");
        companyNameCell.setCellStyle(companyNameCellStyle);
        CellRangeAddress companyRegion = new CellRangeAddress(1, 1, 0, 1);
        sheet.addMergedRegion(companyRegion);
        HSSFCell companyNameValueCell = titlerRow2.createCell(2);
        companyNameValueCell.setCellValue(disinfectionReportExportVO.getCompanyName());
        companyNameValueCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress companyNameRegion = new CellRangeAddress(1, 1, 2, 3);
        sheet.addMergedRegion(companyNameRegion);
        SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("yyyy年MM月");
        HSSFCell createDateCell = titlerRow2.createCell(4);
        createDateCell.setCellValue(simpleDateFormat2.format(new Date()));
        createDateCell.setCellStyle(companyNameValueCellStyle);
        CellRangeAddress createTimeRegion = new CellRangeAddress(1, 1, 4, 5);
        sheet.addMergedRegion(createTimeRegion);
        HSSFCell endDateCell = titlerRow2.createCell(6 + medicineSize);
        endDateCell.setCellValue("截止日期");
        endDateCell.setCellStyle(startTimeCellStyle);
        HSSFCell endDateValueCell = titlerRow2.createCell(7 + medicineSize);
        endDateValueCell.setCellValue(simpleDateFormat.format(disinfectionReportDTO.getCreateTimeEnd()));
        endDateValueCell.setCellStyle(startTimeValueCellStyle);

        HSSFRow titlerRow3 = sheet.createRow(2);
        HSSFCell processProjectCell = titlerRow3.createCell(0);
        processProjectCell.setCellValue("处理项目");
        processProjectCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress processProjectRegion = new CellRangeAddress(2, 3, 0, 1);
        sheet.addMergedRegion(processProjectRegion);
        HSSFCell processObjectCell = titlerRow3.createCell(2);
        processObjectCell.setCellValue("处理对象");
        processObjectCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress processObjectRegion = new CellRangeAddress(2, 3, 2, 2);
        sheet.addMergedRegion(processObjectRegion);
        HSSFCell businessQuantityCell = titlerRow3.createCell(3);
        businessQuantityCell.setCellValue("业务数量");
        businessQuantityCell.setCellStyle(processMethodColorCellStyle);
        CellRangeAddress businessQuantityRegion = new CellRangeAddress(2, 2, 3, 5);
        sheet.addMergedRegion(businessQuantityRegion);
        HSSFCell medicineCell = titlerRow3.createCell(6);
        medicineCell.setCellValue("业务用药量");
        medicineCell.setCellStyle(processMethodColorCellStyle);
        if (medicineSize > 1) {
            CellRangeAddress medicineQuantityRegion = new CellRangeAddress(2, 2, 6, 5 + medicineSize);
            sheet.addMergedRegion(medicineQuantityRegion);
        }

        HSSFRow titlerRow4 = sheet.createRow(3);
        HSSFCell batchCell = titlerRow4.createCell(3);
        batchCell.setCellValue("批次");
        batchCell.setCellStyle(processMethodColorCellStyle);
        HSSFCell processQuantityCell = titlerRow4.createCell(4);
        processQuantityCell.setCellValue("处理量");
        processQuantityCell.setCellStyle(processMethodColorCellStyle);
        HSSFCell goodsCell = titlerRow4.createCell(5);
        goodsCell.setCellValue("对应货物量        (吨）");
        goodsCell.setCellStyle(processMethodColorCellStyle);
        AtomicInteger i = new AtomicInteger(6);
        totalMedicationMap.keySet().stream().forEach(key -> {
            HSSFCell keyCell = titlerRow4.createCell(i.get());
            keyCell.setCellValue(key);
            keyCell.setCellStyle(processMethodColorCellStyle);
            i.getAndIncrement();
        });

        List<DisinfectionReportExportVO.ProcessProjectFirst> firstList = disinfectionReportExportVO.getProcessProjectFirstList();
        AtomicInteger ri = new AtomicInteger(4);
        firstList.stream().forEach(first -> {
            List<DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond> secondList = first.getProcessProjectSecondList();
            final HSSFRow[] firstCategoryRow = new HSSFRow[1];
            secondList.stream().forEach(second -> {
                List<DisinfectionReportExportVO.ProcessProjectFirst.ProcessProjectSecond.ProcessObject> objectList = second.getProcessObjectList();
                final HSSFRow[] secondCategoryRow = new HSSFRow[1];
                if (CollectionUtils.isEmpty(objectList)) {
                    HSSFRow row = sheet.createRow(ri.get());
                    if (firstCategoryRow[0] == null) {
                        firstCategoryRow[0] = row;
                    }
                    HSSFCell secondCell = row.createCell(1);
                    secondCell.setCellValue(second.getName());
                    secondCell.setCellStyle(startTimeValueCellStyle);
                    CellRangeAddress secondRegion = new CellRangeAddress(ri.get(), ri.get(), 1, 2);
                    sheet.addMergedRegion(secondRegion);
                    HSSFCell subTotalOrderQuantityCell = row.createCell(3);
                    subTotalOrderQuantityCell.setCellValue(second.getSubTotalOrderQuantity());
                    subTotalOrderQuantityCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell subTotalProcessQuantityCell = row.createCell(4);
                    subTotalProcessQuantityCell.setCellValue(second.getSubTotalProcessQuantity());
                    subTotalProcessQuantityCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell subTotalGoodsQuantityCell = row.createCell(5);
                    subTotalGoodsQuantityCell.setCellValue(second.getSubTotalGoodsQuantity());
                    subTotalGoodsQuantityCell.setCellStyle(startTimeValueCellStyle);
                    TreeMap<String, BigDecimal> subTotalMedicationMap = second.getSubTotalMedicationMap();
                    AtomicInteger ci = new AtomicInteger(6);
                    subTotalMedicationMap.values().stream().forEach(totalMedication -> {
                        HSSFCell totalMedicationCell = row.createCell(ci.get());
                        totalMedicationCell.setCellValue(totalMedication.doubleValue());
                        totalMedicationCell.setCellStyle(startTimeValueCellStyle);
                        ci.getAndIncrement();
                    });
                    ri.getAndIncrement();
                    return;
                }
                objectList.stream().forEach(object -> {
                    HSSFRow row = sheet.createRow(ri.get());
                    if (secondCategoryRow[0] == null) {
                        secondCategoryRow[0] = row;
                    }
                    if (firstCategoryRow[0] == null) {
                        firstCategoryRow[0] = row;
                    }
                    HSSFCell objectCell = row.createCell(2);
                    objectCell.setCellValue(object.getName());
                    objectCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell orderQuantityCell = row.createCell(3);
                    orderQuantityCell.setCellValue(object.getOrderQuantity());
                    orderQuantityCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell processCell = row.createCell(4);
                    processCell.setCellValue(object.getProcessQuantity());
                    processCell.setCellStyle(startTimeValueCellStyle);
                    HSSFCell goodsQuantityCell = row.createCell(5);
                    goodsQuantityCell.setCellValue(object.getGoodsQuantity());
                    goodsQuantityCell.setCellStyle(startTimeValueCellStyle);
                    TreeMap<String, BigDecimal> subTotalMedicationMap = second.getSubTotalMedicationMap();
                    AtomicInteger ci = new AtomicInteger(6);
                    subTotalMedicationMap.values().stream().forEach(totalMedication -> {
                        HSSFCell totalMedicationCell = row.createCell(ci.get());
                        totalMedicationCell.setCellValue(totalMedication.doubleValue());
                        totalMedicationCell.setCellStyle(startTimeValueCellStyle);
                        ci.getAndIncrement();
                    });
                    ri.getAndIncrement();
                });
                HSSFRow subTotalRow = sheet.createRow(ri.get());
                HSSFCell subTotalCell = subTotalRow.createCell(2);
                subTotalCell.setCellValue("小计");
                subTotalCell.setCellStyle(subTotalCellStyle);
                HSSFCell subTotalOrderQuantityCell = subTotalRow.createCell(3);
                subTotalOrderQuantityCell.setCellValue(second.getSubTotalOrderQuantity());
                subTotalOrderQuantityCell.setCellStyle(subTotalCellStyle);
                HSSFCell subTotalProcessQuantityCell = subTotalRow.createCell(4);
                subTotalProcessQuantityCell.setCellValue(second.getSubTotalProcessQuantity());
                subTotalProcessQuantityCell.setCellStyle(subTotalCellStyle);
                HSSFCell subTotalGoodsQuantityCell = subTotalRow.createCell(5);
                subTotalGoodsQuantityCell.setCellValue(second.getSubTotalGoodsQuantity());
                subTotalGoodsQuantityCell.setCellStyle(subTotalCellStyle);
                TreeMap<String, BigDecimal> subTotalMedicationMap = second.getSubTotalMedicationMap();
                AtomicInteger ci = new AtomicInteger(6);
                subTotalMedicationMap.values().stream().forEach(totalMedication -> {
                    HSSFCell totalMedicationCell = subTotalRow.createCell(ci.get());
                    totalMedicationCell.setCellValue(totalMedication.doubleValue());
                    totalMedicationCell.setCellStyle(subTotalCellStyle);
                    ci.getAndIncrement();
                });
                ri.getAndIncrement();

                HSSFCell secondCell = secondCategoryRow[0].createCell(1);
                secondCell.setCellValue(second.getName());
                secondCell.setCellStyle(startTimeValueCellStyle);
                CellRangeAddress secondRegion = new CellRangeAddress(secondCategoryRow[0].getRowNum(), subTotalRow.getRowNum(), 1, 1);
                sheet.addMergedRegion(secondRegion);
            });
            HSSFRow totalRow = sheet.createRow(ri.get());
            HSSFCell totalCell = totalRow.createCell(1);
            totalCell.setCellValue("合计");
            totalCell.setCellStyle(totalCellStyle);
            CellRangeAddress totalRegion = new CellRangeAddress(ri.get(), ri.get(), 1, 2);
            sheet.addMergedRegion(totalRegion);
            HSSFCell totalOrderQuantityCell = totalRow.createCell(3);
            totalOrderQuantityCell.setCellValue(first.getTotalOrderQuantity());
            totalOrderQuantityCell.setCellStyle(totalCellStyle);
            HSSFCell totalProcessQuantityCell = totalRow.createCell(4);
            totalProcessQuantityCell.setCellValue(first.getTotalProcessQuantity());
            totalProcessQuantityCell.setCellStyle(totalCellStyle);
            HSSFCell totalGoodsQuantityCell = totalRow.createCell(5);
            totalGoodsQuantityCell.setCellValue(first.getTotalGoodsQuantity());
            totalGoodsQuantityCell.setCellStyle(totalCellStyle);
            TreeMap<String, BigDecimal> medicationMap = first.getTotalMedicationMap();
            AtomicInteger ci = new AtomicInteger(6);
            medicationMap.values().stream().forEach(totalMedication -> {
                HSSFCell totalMedicationCell = totalRow.createCell(ci.get());
                totalMedicationCell.setCellValue(totalMedication.doubleValue());
                totalMedicationCell.setCellStyle(totalCellStyle);
                ci.getAndIncrement();
            });
            ri.getAndIncrement();

            HSSFCell firstCell = firstCategoryRow[0].createCell(0);
            firstCell.setCellValue(first.getName());
            firstCell.setCellStyle(startTimeValueCellStyle);
            CellRangeAddress secondRegion = new CellRangeAddress(firstCategoryRow[0].getRowNum(), totalRow.getRowNum(), 0, 0);
            sheet.addMergedRegion(secondRegion);
        });

        return hssfWorkbook;
    }

    private Map<String, BigDecimal> mapListToMap(List<Map<String, BigDecimal>> medicationMapList) {
        Map<String, BigDecimal> subTotalMedicationMap = new LinkedHashMap<>();
        medicationMapList.stream().forEach(medicationMap -> {
            medicationMap.entrySet().forEach(s -> {
                if (subTotalMedicationMap.containsKey(s.getKey())) {
                    subTotalMedicationMap.put(s.getKey(), subTotalMedicationMap.get(s.getKey()).add(s.getValue()));
                } else {
                    subTotalMedicationMap.put(s.getKey(), s.getValue());
                }
            });
        });
        return subTotalMedicationMap;
    }
}
